﻿<# Note
1. If you are running a 64 bit OS, you must use an x86 version of the PowerShell command line, 
2. at least with this version of Oracle.DataAccess which is only compatible on x86
#>

<# Version Info
 1. 输出每个表的信息,信息中加入注释
 2. 美化输出文本的效果
 3. 采用OOP方式重写了输出
 4. 加入批量比较的功能
 5. 加入 "对比时是否考虑数据类型" 功能  -- $compareConfig.IS_COMPARE_DATATYPE
 6. 加入 "输出对比结果时是否输出相等项" 功能  -- $compareConfig.IS_CONTAIN_EQUALITEM
#>

# --------------------------------------------------------Load Other Scripts----------------------------------------------------------------
. ([System.IO.Path]::Combine((Split-Path $MyInvocation.MyCommand.Path),"output.ps1"));      
# . $PSScriptRoot\output.ps1 ;

 # --------------------------------------------------------   Config Begin   ----------------------------------------------------------------
$config_source = @{  # 中间库
                                        HOST = "172.10.0.23"; #127.0.0.1
                                        PORT = "1521";
                                        SID ="orcl";
                                        USER_NAME = "BDCDJ_GXJH";
                                        PASSWORD = "BDCDJ_GXJH";
                                        TABLE_OWNER ="BDCDJ_GXJH";
                                        TABLE_NAME = "BDC_REGN_CF";
                                    };
$config_target = @{ # 业务库
                                        HOST = "172.10.0.23"; #127.0.0.1
                                        PORT = "1523";
                                        SID ="orcl";
                                        USER_NAME = "BDCDJWD";
                                        PASSWORD = "BDCDJWD";
                                        TABLE_OWNER ="BDCDJWD";
                                        TABLE_NAME = "BDC_REGN_CF";            
                                    }
$DesktopPath = [System.Environment]::GetFolderPath([System.Environment+SpecialFolder]::Desktop)
$baseDirPath_BatchGenerate = "{0}/BDCDJ_GXJH_Compare" -F $DesktopPath;
$basicConfig = @{
                                    SOURCE_FILEPATH = "c:/source.txt";
                                    TARGET_FILEPATH="c:/target.txt";
                                    COMPARERESULT_FILEPATH = "{0}/compareResult.txt" -F $DesktopPath;
                               } ;
$compareConfig=@{
    # 对比时是否考虑数据类型
     IS_COMPARE_DATATYPE = $false; #$true;
     # 输出对比结果时是否输出相等项
     IS_CONTAIN_EQUALITEM =$true; #$true;
};
$tablesNeedCompared = @("BDC_XM","BDC_ZRZ","BDC_LJZ","BDC_C","BDC_H", #不动产登记楼盘表属性结构（我们反向写给住建）
                                                    "BDC_REGN_CF","BDC_REGN_DY","BDC_REGN_YG","BDC_REGN_YY","BDC_REGN_FWSYQ", #不动产登记成果属性结构
                                                    "BDC_REC","BDC_H_DJ", #不动产登记过程信息属性结构
                                                   "BDC_QLRLB","BDC_QLR","BDC_CER", #不动产登记权利人、权证属性结构
                                                   "BDC_XMNDZFW_MX","BDC_XMNDZFW" #项目内多幢
                                                   );  
# --------------------------------------------------------Config End-------------------------------------------------------------------      

<#
    //////////////////////////////////////////////Prepare Peroid //////////////////////////////////////////////         
#>                          
function deleteTempFiles ()
{
    if (Test-Path $basicConfig.SOURCE_FILEPATH)
    {
            Remove-Item $basicConfig.SOURCE_FILEPATH
    }
    if (Test-Path $basicConfig.TARGET_FILEPATH)
    {
            Remove-Item $basicConfig.TARGET_FILEPATH
    }
    if (Test-Path $basicConfig.COMPARERESULT_FILEPATH)
    {
            Remove-Item $basicConfig.COMPARERESULT_FILEPATH
    }
        if (Test-Path $baseDirPath_BatchGenerate)
    {
            Remove-Item $baseDirPath_BatchGenerate -Force -Recurse
    }
}

 function prepare ()
 {
     deleteTempFiles;
    # Load the good assembly
     [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient");
 }
 <#
    //////////////////////////////////////////////Functions //////////////////////////////////////////////         
#>     
function getAllFieldInfos ($config)
{
   # Production connexion string 
    $connectStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};" -F
                                    $config.HOST,$config.PORT,$config.SID,$config.USER_NAME,$config.PASSWORD;
    $commandStr = "SELECT  a.column_name, a.data_type, a.data_length,u.comments FROM all_tab_columns a,user_col_comments u WHERE a.table_name =u.table_name AND a.column_name = u.column_name AND a.table_name = '{0}' AND a.owner = '{1}' " -F 
                                    $config.TABLE_NAME.ToUpperInvariant() , $config.TABLE_OWNER.ToUpperInvariant();

    $output_compare =  New-Output4Compare -template "{0}    {1}";
    $output_save =  New-Output4Save -template "{0,-20}    {1,20}({2,4})    {3,10}";
    <#
     $outputConfig = @{
                                            TEMPLATE = "{0}    {1}";
                                            ARRAYRESULT = @();
                                        }; 
     $outputConfig_complete = @{
                                            TEMPLATE = "{0,-20}    {1,20}({2,4})    {3,10}";
                                            ARRAYRESULT = @();
                                        };                                                                                                
    $template = $outputConfig.TEMPLATE;
    $columnArray = $outputConfig.ARRAYRESULT;

    $template_complete = $outputConfig_complete.TEMPLATE;
    $columnArray_complete = $outputConfig_complete.ARRAYRESULT;
    #>
    try
    {
        $connection=New-Object DATA.OracleClient.OracleConnection($connectStr);
        $connection.Open();

        $reader =  (new-Object DATA.OracleClient.OracleCommand($commandStr,$connection)).ExecuteReader();
         while ($reader.Read())
        {
            $column_name = $reader["COLUMN_NAME"].toString();
            $data_type =  $reader["DATA_TYPE"].toString();
            $data_length = $reader["DATA_LENGTH"].toString();
             $comment = $reader["COMMENTS"].toString();

             $output_save.add(@($column_name,$data_type,$data_length,$comment));
             #对比时是否考虑数据类型
             $data_type =  if($compareConfig.IS_COMPARE_DATATYPE){ $data_type} else{ ' ' };
             $output_compare.add(@($column_name,$data_type));
             <#
            $str = $template -F $column_name,$data_type;
            $columnArray +=$str ;
            $str_complete = $template_complete -F $column_name,$data_type,$data_length,$comment;
            $columnArray_complete +=$str_complete ;
            #>
        }
    }
    catch [System.Net.WebException],[System.Exception] 
    {
        Write-Host $Error
        Write-Host "读取字段信息有误,停止操作！！";
         return;
    }
    finally
    {
        $connection.Close();
    }    
    return $output_compare.getResult(),$output_save.getResult();
    #return $columnArray,$columnArray_complete;
}

function generateResult_prepare ()
{
   "#---------------------------------------------------------------------------------------" >>  $basicConfig.COMPARERESULT_FILEPATH
    "=>  代表目标端有,而源端没有" >>  $basicConfig.COMPARERESULT_FILEPATH 
    "<=  代表源端有,而目标端没有" >>  $basicConfig.COMPARERESULT_FILEPATH 
    "#---------------------------------------------------------------------------------------" >>  $basicConfig.COMPARERESULT_FILEPATH
}

function generateResult ()
{
    generateResult_prepare;

    $source =  getAllFieldInfos $config_source 
    $target = getAllFieldInfos $config_target

    $source_compared = $source[0];
    $target_compared = $target[0] ;
   
    #  是否输出相同项
    if ($compareConfig.IS_CONTAIN_EQUALITEM)
    {
        Compare-Object  $source_compared $target_compared -IncludeEqual >> $basicConfig.COMPARERESULT_FILEPATH 
    }
    else
    {
         Compare-Object  $source_compared $target_compared >> $basicConfig.COMPARERESULT_FILEPATH 
    }
       

    $source_saved = $source[1] 
    $target_saved = $target[1] 
    $source_saved >> $basicConfig.SOURCE_FILEPATH ;
     $target_saved >> $basicConfig.TARGET_FILEPATH ;
}

function makeSureDirPathExist ($filePath)
{
    $dirPath = [System.IO.Path]::GetDirectoryName($filePath)
    if ( (-not (Test-Path $dirPath)) -or (isFile $dirPath))
    {
        New-Item $dirPath -type directory
    }
}

function generateAllResult_prepare ($tableName)
{
    $config_source.TABLE_NAME = $tableName;
    $config_target.TABLE_NAME = $tableName;

    
    $basicConfig.SOURCE_FILEPATH = "{0}/{1}/source.txt" -F $baseDirPath_BatchGenerate,$tableName;
    $basicConfig.TARGET_FILEPATH = "{0}/{1}/target.txt" -F $baseDirPath_BatchGenerate,$tableName;
    $basicConfig.COMPARERESULT_FILEPATH =  "{0}/{1}/compareResult.txt" -F $baseDirPath_BatchGenerate,$tableName;         

    makeSureDirPathExist  $basicConfig.SOURCE_FILEPATH
}

function  generateAllResult()
{
    foreach ($tableName in $tablesNeedCompared)
    {
        generateAllResult_prepare $tableName;
        generateResult
    }
}

 <#
    //////////////////////////////////////////////Call //////////////////////////////////////////////         
#>
prepare;
#generateResult;
generateAllResult




<#
$commandStr = "SELECT sysdate FROM dual ";
$result = (new-Object DATA.OracleClient.OracleCommand($commandStr,$connection)).ExecuteScalar();
$result >> c:/test.txt
#>


